package com.cn.wanxi.dao.impl;

import com.cn.wanxi.dao.ProductDao;
import com.cn.wanxi.model.ProductModel;
import com.cn.wanxi.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProductImpl implements ProductDao {
    @Override
    public List<ProductModel> findAll(ProductModel productModel) {
        String sql = "select p.* ,c.name cuisineName from product p\n" +
                "left join cuisine c\n" +
                "on  p.cuisine_id=c.id " +
                " order by id desc ";
        sql += " limit " + (productModel.getPage() - 1) * +productModel.getLimit() + ", " + productModel.getLimit() + ";";
        ResultSet resultSet = JDBC.query(sql);
        List<ProductModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                ProductModel model = new ProductModel();
                model.setId(resultSet.getInt("id"));
                model.setName(resultSet.getString("name"));
                model.setNormalPrice(resultSet.getBigDecimal("price"));
                model.setIsShow(resultSet.getInt("is_show"));
                model.setIsRecommend(resultSet.getInt("is_recommend"));
                model.setWhereShow(resultSet.getInt("where_show"));
                model.setImgHref(resultSet.getString("img_href"));
                model.setEnable(resultSet.getInt("enable"));
                model.setCuisineName(resultSet.getString("cuisineName"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int getCount(ProductModel productModel) {
        String sql = "select count(*) count  from product p " +
                " left join cuisine c " +
                " on  p.cuisine_id=c.id ";
        if (productModel.getCuisineId() != -1) {
            sql += " and p.cuisine_id=" + productModel.getCuisineId();
        }
        ResultSet resultSet = JDBC.query(sql);
        int count = 0;
        try {
            while (resultSet.next()) {
                count = resultSet.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    @Override
    public int updateEnable(ProductModel model) {
        return 0;
    }

    @Override
    public int updateField(ProductModel model) {
        return 0;
    }

    @Override
    public int delete(ProductModel model) {
        String sql = "delete from product where id=" + model.getId();
        return JDBC.update(sql);
    }

    @Override
    public int add(ProductModel model) {
        String sql = "INSERT INTO `product` (" +
                "`name`, `normal_price`, `price`, " +
                "`content`, `is_show`, `is_recommend`, " +
                "`img_href`, `create_time`, `update_time`, " +
                "`remark`, `enable`, `where_show`, " +
                "`cuisine_id`) VALUES (" +
                "'" + model.getName() + "', '" + model.getNormalPrice() + "', '" + model.getMarkedPrice() + "'," +
                " '" + model.getContent() + "', '1', '" + model.getIsRecommend() + "'," +
                " '" + model.getImgHref() + "', now(),now()," +
                " '', '1', '1'," +
                " '" + model.getCuisineId() + "');\n";
        return JDBC.update(sql);
    }

    @Override
    public ProductModel findById(ProductModel productModel) {
        String sql = "select p.* ,c.name cuisineName from product p\n" +
                "left join cuisine c\n" +
                "on  p.cuisine_id=c.id " +
                " and p.id= " + productModel.getId();
        ResultSet resultSet = JDBC.query(sql);
        ProductModel model = new ProductModel();
        try {
            while (resultSet.next()) {

                model.setId(resultSet.getInt("id"));
                model.setName(resultSet.getString("name"));
                model.setNormalPrice(resultSet.getBigDecimal("normal_price"));
                model.setIsShow(resultSet.getInt("is_show"));
                model.setIsRecommend(resultSet.getInt("is_recommend"));
                model.setWhereShow(resultSet.getInt("where_show"));
                model.setImgHref(resultSet.getString("img_href"));
                model.setEnable(resultSet.getInt("enable"));
                model.setCuisineName(resultSet.getString("cuisineName"));
                model.setContent(resultSet.getString("content"));
                model.setMarkedPrice(resultSet.getBigDecimal("price"));
                model.setCuisineId(resultSet.getInt("cuisine_id"));
//                model.setIsTop(resultSet.getInt("is_top"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;
    }

    @Override
    public int update(ProductModel model) {
        String sql = "UPDATE `product` SET " +
                "`name` = '" + model.getName() + "', " +
                "`normal_price` = '" + model.getNormalPrice() + "'," +
                " `price` = '" + model.getMarkedPrice() + "', " +
                "`content` = '" + model.getContent() + "', " +
                "`is_show` = '" + model.getIsShow() + "'," +
                " `is_recommend` = '" + model.getIsRecommend() + "', ";
        if (model.getImgHref() != null && !"".equals(model.getImgHref())) {
            sql += "`img_href` = '" + model.getImgHref() + "', ";
        }

        sql +=
                " `update_time` = now()," +
                        " `remark` = '1', " +
                        "`enable` = '1'," +
                        " `where_show` = '2'," +
                        " `cuisine_id` = '3'" +
                        " WHERE (`id` = '" + model.getId() + "');\n";
        return JDBC.update(sql);
    }

    /**
     * 默认2条置顶，2条热点，其余普通
     *
     * @param productModel
     * @return
     */
    @Override
    public List<ProductModel> getProductModelList(ProductModel productModel) {
        List<ProductModel> list = new ArrayList<>();
//        首先查询热点
        List<ProductModel> hotList = getHotList(productModel);
        for (ProductModel model : hotList) {
            list.add(model);
        }
//        查询置顶
        List<ProductModel> topList = getTopList(productModel);
        for (ProductModel model : topList) {
            list.add(model);
        }
//        普通产品条数应该根据前面两个的条数进行改变
        productModel.setLimit(productModel.getLimit() - list.size());
        List<ProductModel> normalList = getNormalList(productModel);
        for (ProductModel model : normalList) {
            list.add(model);
        }
        return list;

    }

    private List<ProductModel> getHotList(ProductModel productModel) {
        String sql = "select p.* ,c.name cuisineName from product p\n" +
                " join cuisine c\n" +
                " on  p.cuisine_id=c.id and p.cuisine_id=" + productModel.getCuisineId() +
                " and p.where_show=" + productModel.getWhereShow() +
                " and is_show=1" +
                " order by id desc ";

        sql += " limit " + (productModel.getPage() - 1) * +2 + ", 2;";
        ResultSet resultSet = JDBC.query(sql);
        List<ProductModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                ProductModel model = new ProductModel();
                model.setId(resultSet.getInt("id"));
                model.setName(resultSet.getString("name"));
                model.setNormalPrice(resultSet.getBigDecimal("price"));
                model.setIsShow(resultSet.getInt("is_show"));
                model.setIsRecommend(resultSet.getInt("is_recommend"));
                model.setWhereShow(resultSet.getInt("where_show"));
                model.setImgHref(resultSet.getString("img_href"));
                model.setEnable(resultSet.getInt("enable"));
                model.setCuisineName(resultSet.getString("cuisineName"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    private List<ProductModel> getTopList(ProductModel productModel) {
        String sql = "select p.* ,c.name cuisineName from product p\n" +
                " join cuisine c\n" +
                " on  p.cuisine_id=c.id and p.cuisine_id=" + productModel.getCuisineId() +
                " and p.where_show=" + productModel.getWhereShow() +
                " and is_recommend=1" +
                " order by id desc ";

        sql += " limit " + (productModel.getPage() - 1) * +2 + ", " + 2 + ";";
        ResultSet resultSet = JDBC.query(sql);
        List<ProductModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                ProductModel model = new ProductModel();
                model.setId(resultSet.getInt("id"));
                model.setName(resultSet.getString("name"));
                model.setNormalPrice(resultSet.getBigDecimal("price"));
                model.setIsShow(resultSet.getInt("is_show"));
                model.setIsRecommend(resultSet.getInt("is_recommend"));
                model.setWhereShow(resultSet.getInt("where_show"));
                model.setImgHref(resultSet.getString("img_href"));
                model.setEnable(resultSet.getInt("enable"));
                model.setCuisineName(resultSet.getString("cuisineName"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    private List<ProductModel> getNormalList(ProductModel productModel) {
        String sql = "select p.* ,c.name cuisineName from product p\n" +
                " join cuisine c\n" +
                " on  p.cuisine_id=c.id and p.cuisine_id=" + productModel.getCuisineId() +
                " and p.where_show=" + productModel.getWhereShow() +
                " and is_recommend!=1 and is_show!=1" +
                " order by id desc ";

        sql += " limit " + (productModel.getPage() - 1) * +productModel.getLimit() + ", " + productModel.getLimit() + ";";
        ResultSet resultSet = JDBC.query(sql);
        List<ProductModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                ProductModel model = new ProductModel();
                model.setId(resultSet.getInt("id"));
                model.setName(resultSet.getString("name"));
                model.setNormalPrice(resultSet.getBigDecimal("price"));
                model.setIsShow(resultSet.getInt("is_show"));
                model.setIsRecommend(resultSet.getInt("is_recommend"));
                model.setWhereShow(resultSet.getInt("where_show"));
                model.setImgHref(resultSet.getString("img_href"));
                model.setEnable(resultSet.getInt("enable"));
                model.setCuisineName(resultSet.getString("cuisineName"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 前端产品页面的总条数
     *
     * @param productModel
     * @return
     */
    @Override
    public int getFrontCount(ProductModel productModel) {
        String sql = "select count(*) count  from product p " +
                " join cuisine c " +
                " on  p.cuisine_id=c.id " +
                " and p.where_show=" + productModel.getWhereShow();
        if (productModel.getCuisineId() != -1) {
            sql += " and p.cuisine_id=" + productModel.getCuisineId();
        }
        ResultSet resultSet = JDBC.query(sql);
        int count = 0;
        try {
            while (resultSet.next()) {
                count = resultSet.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    /**
     * 通过前端页面的id应该查询出三条数据
     *
     * @param model
     * @return
     */
    @Override
    public List<ProductModel> findFrontById(ProductModel model) {
        String sql = "(select * from product where id=(select min(id) from product where id>" + model.getId() + ") order by id desc)\n" +
                "union \n" +
                "(select * from product where id=" + model.getId() + " order by id desc)\n" +
                "union\n" +
                "(select * from product where id=(select max(id) from product where id<" + model.getId() + ") order by id desc)";
        ResultSet resultSet = JDBC.query(sql);
        List<ProductModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                ProductModel productModel = new ProductModel();
                productModel.setId(resultSet.getInt("id"));
                productModel.setName(resultSet.getString("name"));
                productModel.setNormalPrice(resultSet.getBigDecimal("price"));
                productModel.setIsShow(resultSet.getInt("is_show"));
                productModel.setIsRecommend(resultSet.getInt("is_recommend"));
                productModel.setWhereShow(resultSet.getInt("where_show"));
                productModel.setImgHref(resultSet.getString("img_href"));
                productModel.setEnable(resultSet.getInt("enable"));
//                productModel.setCuisineName(resultSet.getString("cuisineName"));
                list.add(productModel);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}
